#include "head.h"
// 查询数据
#include <stdio.h>
#include <sqlite3.h>
#include <string.h>


sqlite3 *init_db(const char *db_name)
{

    sqlite3 *db;
    int rc = sqlite3_open(db_name, &db);
    if (rc)
    {
        fprintf(stderr, "无法打开数据库: %s\n", sqlite3_errmsg(db));
        return NULL;
    }
    return db;
}

// 执行 SQL 语句（用于创建表、插入数据等）
int execute_sql(sqlite3 *db, const char *sql)
{
    char *err_msg = 0;
    int rc = sqlite3_exec(db, sql, 0, 0, &err_msg);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "SQL 错误: %s\n", err_msg);
        sqlite3_free(err_msg);
    }
    return rc;
}






// 查询数据函数
int query_data(sqlite3 *db, const char *sql, int *ID, char *username)
{
    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "无法准备 SQL: %s\n", sqlite3_errmsg(db));
        return rc;
    }

    // 执行查询并获取结果
    rc = sqlite3_step(stmt);
    if (rc == SQLITE_ROW)
    {
        // 获取查询结果的列数据
        int id = sqlite3_column_int(stmt, 0);
        const unsigned char *name = sqlite3_column_text(stmt, 1);

        printf("ID: %d, Name: %s\n", id, name);

        // 赋值给传入的指针参数
        if (ID != NULL)
        {
            *ID = id;
        }
        if (username != NULL && name != NULL)
        {
            strcpy(username, (const char *)name);
        }
    }
    else if (rc == SQLITE_DONE)
    {
        fprintf(stderr, "没有符合条件的记录。\n");
    }
    else
    {
        fprintf(stderr, "查询失败: %s\n", sqlite3_errmsg(db));
    }

    sqlite3_finalize(stmt); // 释放语句对象
    return rc == SQLITE_ROW ? SQLITE_OK : rc;
}






// 关闭数据库
void close_db(sqlite3 *db)
{
    if (db)
    {
        sqlite3_close(db);
    }
}

int create_table(sqlite3 *db)
{

    const char *create_table_sql = "CREATE TABLE IF NOT EXISTS Users (ID INTEGER PRIMARY KEY, username TEXT UNIQUE, Password TEXT,IP TEXT ,port TEXT ,Sock_fd INT ,state TEXT);";
    execute_sql(db, create_table_sql);

    const char *create_table_sql2 = "CREATE TABLE IF NOT EXISTS Friends (ID INTEGER PRIMARY KEY, Name TEXT ,name_ID INT ,Friends_name  TEXT,Friends_ID INT, state TEXT,UNIQUE (name_ID, Friends_ID));";
    execute_sql(db, create_table_sql2);

    const char *create_table_sql3 = "CREATE TABLE IF NOT EXISTS group_data (ID INTEGER PRIMARY KEY,Name TEXT ,name_id INT ,Group_name TEXT , admin INT ,UNIQUE (name_id, Group_name));";
    execute_sql(db, create_table_sql3);

    const char *create_table_sql4 = "CREATE TABLE IF NOT EXISTS Msg_data (ID INTEGER PRIMARY KEY,Name TEXT ,name_Id INT , Msg TEXT );";
    execute_sql(db, create_table_sql4);
    return 0;

}



// 插入用户表
int insert_user(sqlite3 *db,char *username,char *password) {
    char sql[256]; // 存储插入 SQL 语句
    snprintf(sql, sizeof(sql), "INSERT INTO users (username, password) VALUES ('%s', '%s');", username, password);
    return execute_sql(db, sql);
}



// 发送朋友圈消息
int insert_Circle(sqlite3 *db,char* name, int ID,char *msg) 
{
    char sql[256]; // 存储插入 SQL 语句
    snprintf(sql, sizeof(sql), "INSERT INTO Msg_data (name, name_ID ,Msg) VALUES ('%s', %d,'%s');", name,ID, msg);
    return execute_sql(db, sql);
}



//注册用户
int enroll_user(sqlite3 *db, char *username, char *password)
{
    
    insert_user(db,username,password);
    return 0;
}

int insert_Friends(sqlite3 *db,char *name,int name_ID,char *Friends ,int Friends_ID,char *state)
{
    char sql[256]; // 存储插入 SQL 语句
    snprintf(sql, sizeof(sql), "INSERT INTO Friends (name, name_ID,Friends_name,Friends_ID,state) VALUES ('%s',%d,'%s',%d,'%s');", name, name_ID,Friends,Friends_ID,state);
    return execute_sql(db, sql);
}




int login_user(sqlite3 *db, const char *username, const char *password, int *user_id)
{
    const char *sql = "SELECT id FROM Users WHERE username = ? AND password = ?;";
    sqlite3_stmt *stmt;

    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return rc;
    }

    sqlite3_bind_text(stmt, 1, username, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, password, -1, SQLITE_STATIC);

    rc = sqlite3_step(stmt);
    if (rc == SQLITE_ROW)
    {
        // 登录成功，获取用户ID
        *user_id = sqlite3_column_int(stmt, 0);
        printf("Login successful: %s, return id: %d\n", username, *user_id);
    }
    else
    {
        printf("Login failed\n");
    }

    sqlite3_finalize(stmt);
    return rc;
}

int login_user_back_id(sqlite3 *db, const char *username, const char *password)
{
    int user_id;
    if (login_user(db, username, password, &user_id) == SQLITE_ROW)
    {
        printf("User ID: %d\n", user_id);
        return user_id;
    }
    return -1;
}
int sql_init_sp(const char* db_name)
{

    sqlite3 *db = init_db(db_name);
    if (!db)
    {
        return EXIT_FAILURE;
    }

    return 0;
}

char* sql_get_username_by_id(sqlite3 *db, int id) 
{
    const char *sql = "SELECT username FROM users WHERE id = ?";
    sqlite3_stmt *stmt;
    static char username[128]; // 使用静态数组返回用户名

    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "无法准备 SQL: %s\n", sqlite3_errmsg(db));
        return NULL;
    }

    sqlite3_bind_int(stmt, 1, id);

    if (sqlite3_step(stmt) == SQLITE_ROW)
    {
        const unsigned char *name = sqlite3_column_text(stmt, 0);
        strncpy(username, (const char *)name, sizeof(username) - 1);
        username[sizeof(username) - 1] = '\0'; // 确保字符串以 null 结尾
        sqlite3_finalize(stmt);
        return username;
    }

    sqlite3_finalize(stmt);
    return NULL; // 未找到记录
}



char* sql_get_request_by_id(sqlite3 *db, int id ,int Friends_ID) 
{
    const char *sql = "SELECT state FROM Friends WHERE name_id = ? AND  Friends_ID =?";
    sqlite3_stmt *stmt;
    static char state_data[128]; // 使用静态数组返回用户名

    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "无法准备 SQL: %s\n", sqlite3_errmsg(db));
        return NULL;
    }

    sqlite3_bind_int(stmt, 1, id);
    sqlite3_bind_int(stmt, 2, Friends_ID);


    if (sqlite3_step(stmt) == SQLITE_ROW)
    {
        const unsigned char *state = sqlite3_column_text(stmt, 0);
        strncpy(state_data, (const char *)state, sizeof(state_data) - 1);
        state_data[sizeof(state_data) - 1] = '\0'; // 确保字符串以 null 结尾
        sqlite3_finalize(stmt);
        return state_data;
    }

    sqlite3_finalize(stmt);
    return NULL; // 未找到记录
}






char* sql_print_msgs_by_id(sqlite3 *db, int id,char *name)
{
    sqlite3_stmt *stmt;
    const char *sql = "SELECT msg  , ID  FROM msg_data WHERE name_id = ?";

    // 准备 SQL 语句
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) != SQLITE_OK)
    {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        return NULL;
    }

    // 绑定 name_ID 参数
    sqlite3_bind_int(stmt, 1, id);

    // 定义静态缓冲区用于返回数据
    static char send_buff[BUFFER_SIZE_MAX * 10];  // 假设缓冲区可以容纳多个消息
    memset(send_buff, 0, sizeof(send_buff));

    // 拼接标题


    // 执行查询并输出结果
    while (sqlite3_step(stmt) == SQLITE_ROW)
    {   
        const unsigned char *tempmsg = sqlite3_column_text(stmt, 0);
        int  ID = sqlite3_column_int(stmt, 1);
        if (tempmsg)
        {
            // 临时缓冲区存放单条消息
            char send_buf[256];
            memset(send_buf, 0, sizeof(send_buf));
            
            // 格式化单条消息为 "name: message\n" 格式
            sprintf(send_buf, "[%d]%s: %s\n",ID, name, tempmsg);
            
            // 将单条消息拼接到 send_buff 中
            if (strlen(send_buff) + strlen(send_buf) < sizeof(send_buff))
            {
                strcat(send_buff, send_buf);
            }
            else
            {
                fprintf(stderr, "Buffer overflow: cannot append more messages.\n");
                break;
            }
            
            // 打印每条消息
            printf("%s: %s\n", name, tempmsg);
        }
    }

    // 清理资源
    sqlite3_finalize(stmt);

    return send_buff;
}













int sql_get_id_by_username(sqlite3 *db, const char *username)
{
    const char *sql = "SELECT id FROM users WHERE username = ?";
    sqlite3_stmt *stmt;
    int id = -1; // 默认值为 -1，表示未找到

    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "无法准备 SQL: %s\n", sqlite3_errmsg(db));
        return -1;
    }

    sqlite3_bind_text(stmt, 1, username, -1, SQLITE_STATIC);

    if (sqlite3_step(stmt) == SQLITE_ROW)
    {
        id = sqlite3_column_int(stmt, 0);
        printf(" ");
    }

    sqlite3_finalize(stmt);
    return id; // 返回找到的 ID 或 -1
}

int sql_get_sock_fd_by_id(sqlite3 *db, int ID)
{
    const char *sql = "SELECT sock_fd FROM users WHERE Id = ?";
    sqlite3_stmt *stmt;
    int sock_fd = -1; // 默认值为 -1，表示未找到

    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "无法准备 SQL: %s\n", sqlite3_errmsg(db));
        return -1;
    }

    sqlite3_bind_int(stmt, 1, ID);

    if (sqlite3_step(stmt) == SQLITE_ROW)
    {
        sock_fd = sqlite3_column_int(stmt, 0);
        printf(" ");
    }

    sqlite3_finalize(stmt);
    return sock_fd; // 返回找到的 ID 或 -1
}


char* sql_get_state_by_id(sqlite3 *db, int id)
{
    const char *sql = "SELECT state FROM users WHERE id = ?";
    sqlite3_stmt *stmt;
    static char state[128]; // 使用静态数组返回用户名

    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "无法准备 SQL: %s\n", sqlite3_errmsg(db));
        return NULL;
    }

    sqlite3_bind_int(stmt, 1, id);

    if (sqlite3_step(stmt) == SQLITE_ROW)
    {
        const unsigned char *temp = sqlite3_column_text(stmt, 0);
        strncpy(state, (const char *)temp, sizeof(state) - 1);
        state[sizeof(state) - 1] = '\0'; // 确保字符串以 null 结尾
        sqlite3_finalize(stmt);
        return state;
    }

    sqlite3_finalize(stmt);
    return NULL; // 未找到记录
}


int Create_group(sqlite3 *db,int id,char *Create_name,char * group_name)
{
//   const char *create_table_sql3 = "CREATE TABLE IF NOT EXISTS group_data (ID INTEGER PRIMARY KEY,Name TEXT ,Group_name TEXT , admin INT );";
    char sql[256]; // 存储插入 SQL 语句
    snprintf(sql, sizeof(sql), "INSERT INTO group_data (name, name_id,Group_name,admin) VALUES ('%s',%d,'%s',%d);", Create_name, id,group_name,1);
    return execute_sql(db, sql); 
}

int add_group(sqlite3 *db,int id,char *Create_name,char * group_name)
{
//   const char *create_table_sql3 = "CREATE TABLE IF NOT EXISTS group_data (ID INTEGER PRIMARY KEY,Name TEXT ,Group_name TEXT , admin INT );";
    char sql[256]; // 存储插入 SQL 语句
    snprintf(sql, sizeof(sql), "INSERT INTO group_data (name, name_id,Group_name,admin) VALUES ('%s',%d,'%s',%d);", Create_name, id,group_name,0);
    return execute_sql(db, sql); 
}

int delete_Group(sqlite3 *db,int ID,char * group_name)
{
    const char *sql = "DELETE FROM group_data WHERE group_name = ? AND name_ID = ?";
    sqlite3_stmt *stmt;
    int sc = -1; // 默认值为 -1，表示未找到

    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "无法准备 SQL: %s\n", sqlite3_errmsg(db));
        return -1;
    }
    sqlite3_bind_text(stmt, 1, group_name, -1, SQLITE_STATIC);
    sqlite3_bind_int(stmt, 2, ID);
  
    if (sqlite3_step(stmt) == SQLITE_DONE )
    {
        sc = sqlite3_column_int(stmt, 0);
        printf(" OK _  %d \n",sc);
    }
    sqlite3_finalize(stmt);
    return sc; // 返回找到的 ID 或 -1
}

int delete_Friend(sqlite3 *db,int my_ID,int friend_ID )
{
        const char *sql = "DELETE FROM Friends WHERE name_ID = ? AND friends_ID = ?";
    sqlite3_stmt *stmt;
    int sc = -1; // 默认值为 -1，表示未找到

    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "无法准备 SQL: %s\n", sqlite3_errmsg(db));
        return -1;
    }
    sqlite3_bind_int(stmt, 1, my_ID);
    sqlite3_bind_int(stmt, 2, friend_ID);
    if (sqlite3_step(stmt) == SQLITE_DONE )
    {
        sc = sqlite3_column_int(stmt, 0);
        printf(" OK _  %d \n",sc);
    }
    sqlite3_finalize(stmt);
    return sc; // 返回找到的 ID 或 -1

}

int select_circle_msg(sqlite3 *db, int my_ID ,int msg_ID)
{
   
    const char *sql = "SELECT ID FROM  msg_data WHERE name_ID = ? AND ID = ?";
    sqlite3_stmt *stmt;
    int sock_fd = -1; // 默认值为 -1，表示未找到

    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "无法准备 SQL: %s\n", sqlite3_errmsg(db));
        return -1;
    }

    sqlite3_bind_int(stmt, 1, my_ID);
    sqlite3_bind_int(stmt, 2, msg_ID);
    if (sqlite3_step(stmt) == SQLITE_ROW)
    {
        sock_fd = sqlite3_column_int(stmt, 0);
        printf(" ");
    }
    sqlite3_finalize(stmt);
    return sock_fd; // 返回找到的 ID 或 -1
}





int delete_circle_msg(sqlite3 *db,int my_ID,int msg_ID )
{
    const char *sql = "DELETE FROM msg_data WHERE name_ID = ? AND ID = ?";
    sqlite3_stmt *stmt;
    int sc = -1; // 默认值为 -1，表示未找到

    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "无法准备 SQL: %s\n", sqlite3_errmsg(db));
        return -1;
    }
    sqlite3_bind_int(stmt, 1, my_ID);
    sqlite3_bind_int(stmt, 2, msg_ID);
    if (sqlite3_step(stmt) == SQLITE_DONE )
    {
        sc = sqlite3_column_int(stmt, 0);
        printf(" OK _  %d \n",sc);
    }
    sqlite3_finalize(stmt);
    return sc; // 返回找到的 ID 或 -1
}




